package com._520it.day1._05_review;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

/**
 * 思路：贾琏欲执事
 * java.sql/javax.sql包
 * 1.加载注册驱动
 * 2.获取连接对象（connection）
 * 3.创建/获取语句对象
 * 4.执行sql
 * 5.释放资源
 *
 */
public class CRUDTest {
	
	@Test
	public void testSave() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
		Statement st = conn.createStatement();
		String sql="INSERT INTO t_student(name,age) VALUES('西门吹雪',32)";
		st.executeUpdate(sql);
		st.close();
		conn.close();
	}
	
	@Test
	public void testDelete() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
		Statement st = conn.createStatement();
		String sql="DELETE FROM t_student WHERE ID=1";
		st.executeUpdate(sql);
		st.close();
		conn.close();
	}
	
	@Test
	public void testUpdate() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
		Statement st = conn.createStatement();
		String sql="UPDATE t_student SET name='乔峰',age=29 WHERE id=2";
		st.executeUpdate(sql);
		st.close();
		conn.close();
	}
	
	/**
	 * 处理结果集
	 * @throws Exception
	 */
	@Test
	public void testGetSingle() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
		Statement st = conn.createStatement();
		String sql= "SELECT * FROM t_student WHERE id=2";
		ResultSet rs = st.executeQuery(sql);
		if(rs.next()){
			long id = rs.getLong("id");
			String name = rs.getString("name");
			int age = rs.getInt("age");
			System.out.println(id+name+age);
		}
		rs.close();
		st.close();
		conn.close();
	}
	
	@Test
	public void tesGetAll() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
		Statement st = conn.createStatement();
		String sql= "SELECT * FROM t_student";
		ResultSet rs = st.executeQuery(sql);
		while(rs.next()){
			long id = rs.getLong("id");
			String name = rs.getString("name");
			int age = rs.getInt("age");
			System.out.println(id+name+age);
		}
		rs.close();
		st.close();
		conn.close();
	}
}
